﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;

namespace Nova_DAL
{
    public class Menu
    {
        DataSet ds = null;
        string strSQL = "";
        string UserMenuList = "";          //用户菜单字段
        Nova_DAL.DbHelper dbHelper = new DbHelper();
        DbCommand cmd = null;
        int i = 0;
        int strMenuID = 0;

        ///<summary>
        ///获取菜单列表
        ///</summary>
        ///<param name="sQL">SQL</param>
        ///<returns>返回DataSet</returnss>
        ///Writer：[王成友]
        ///Create Date： [2010-5-21]
        ///</summary>
        public DataSet DataListMenu()
        {
            try
            {
                strSQL = "select id,iNode,iParent,vText,vUrl,vTarget,vImg,vMenuNo,iOrder from tbMenu order by iOrder asc,id desc";
                cmd = dbHelper.GetSqlStringCommond(strSQL);
                ds = dbHelper.ExecuteDataSet(cmd);
            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }
            return ds;
        }

        ///<summary>
        ///获取指定菜单列表
        ///</summary>
        ///<param name="sQL">SQL</param>
        ///<returns>返回DataSet</returnss>
        ///Writer：[夏鑫]
        ///Create Date： [2010-6-4]
        ///</summary>
        public DataSet DataListMenu(string strvPemit)
        {
            try
            {
                strSQL = "select id,vText,iOrder,iParent from tbMenu where id in (" + strvPemit + ") order by iOrder asc,id desc";
                cmd = dbHelper.GetSqlStringCommond(strSQL);
                ds = dbHelper.ExecuteDataSet(cmd);
            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }
            return ds;
        }

        ///<summary>
        ///获取菜单id
        ///</summary>
        ///<param name="菜单名称">菜单名称</param>
        ///<returns>返回菜单id</returnss>
        ///Writer：[王成友]
        ///Create Date： [2010-6-3]
        ///</summary>
        public int BackMenu(string vText)
        {
            try
            {
                strSQL = "select id from tbMenu where vText = @vText";
                DbCommand cmd = dbHelper.GetSqlStringCommond(strSQL);
                cmd = dbHelper.GetSqlStringCommond(strSQL);
                dbHelper.AddInParameter(cmd, "@vText", DbType.String, vText);
                strMenuID = (int)dbHelper.ExecuteScalar(cmd);
            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }
            return strMenuID;
        }


        ///<summary>
        ///获取菜单详细
        ///</summary>
        ///<param name="sQL">SQL</param>
        ///<returns>返回DataSet</returnss>
        ///Writer：[夏鑫]
        ///Create Date： [2010-5-27]
        ///</summary>
        public DataSet DataListMenu(int ID)
        {
            try
            {
                strSQL = "select id,iNode,iParent,vText,vUrl,vTarget,vImg,vMenuNo,iOrder from tbMenu where id='" + ID + "'";
                cmd = dbHelper.GetSqlStringCommond(strSQL);
                ds = dbHelper.ExecuteDataSet(cmd);
            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }
            return ds;
        }

        ///<summary>
        ///显示用户用户编号菜单所有大类ID
        ///</summary>
        ///<param name="sQL">vGroupNo工作组编号</param>
        ///<returns>返回用户编号菜单所有的id</returnss>
        ///Writer：[王成友]
        ///Create Date： [2010-5-21]
        ///</summary>
        public string UserListMenu(string vGroupNo)
        {
            try
            {
                strSQL = "select vPermit from tbGroupPermit where vGroupNo = @vGroupNo";
                cmd = dbHelper.GetSqlStringCommond(strSQL);
                dbHelper.AddInParameter(cmd, "@vGroupNo", DbType.String, vGroupNo);
                UserMenuList = (string)dbHelper.ExecuteScalar(cmd);

            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }
            return UserMenuList;
        }


        ///<summary>
        ///显示用户对应的大类权限ID
        ///</summary>
        ///<param name="sQL">SQL</param>
        ///<returns>返回用户编号所对应的大类权限id</returnss>
        ///Writer：[王成友]
        ///Create Date： [2010-5-21]
        ///</summary>
        public DataSet UserRole(string vGroupNo)
        {
            UserMenuList = UserListMenu(vGroupNo);

            if (UserMenuList == "")
            {
                strSQL = "select vText,id,vTarget,vImg,vUrl,iNode,iParent from tbMenu where iParent ='0' order by iOrder";
            }
            else
            {
                strSQL = "select vText,id,vTarget,vImg,vUrl,iNode,iParent from tbMenu where iParent ='0' and convert(varchar(100),id) in (" + UserMenuList + ") order by iOrder";
            }

            cmd = dbHelper.GetSqlStringCommond(strSQL);
            ds = dbHelper.ExecuteDataSet(cmd);
            return ds;
        }


        ///<summary>
        ///显示用户对应的小类权限ID
        ///</summary>
        ///<param name="sQL">SQL</param>
        ///<returns>返回用户编号所对应的小类权限id</returnss>
        ///Writer：[王成友]
        ///Create Date： [2010-5-21]
        ///</summary>
        public DataSet UserRole(string vGroupNo, string iNode)
        {
            UserMenuList = UserListMenu(vGroupNo);
            if (UserMenuList == "")
            {
                strSQL = "select vText,id,vTarget,vImg,vUrl,iNode,iParent from tbMenu where iParent <>'0' order by iOrder";
            }
            else
            {
                strSQL = "select vText,id,vTarget,vImg,vUrl,iNode,iParent from tbMenu where iParent='" + iNode + "' and convert(varchar(100),id) in (" + UserMenuList + ")  order by iOrder";
            }
            cmd = dbHelper.GetSqlStringCommond(strSQL);
            ds = dbHelper.ExecuteDataSet(cmd);
            return ds;

        }




        ///<summary>
        ///增加菜单列表
        ///</summary>
        ///<param name="sQL">Menu</param>
        ///<returns>返回i</returnss>
        ///Writer：[王成友]
        ///Create Date： [2010-5-21]
        ///</summary>
        public int AddMenu(Nova_MOD.Menu Menu)
        {
            try
            {
                strSQL = "insert into tbMenu(iNode,iParent,vText,vUrl,vTarget,vImg,vMenuNo,iOrder) values(@iNode,@iParent,@vText,@vUrl,@vTarget,@vImg,@vMenuNo,@iOrder)";
                cmd = dbHelper.GetSqlStringCommond(strSQL);
                dbHelper.AddInParameter(cmd, "@iNode", DbType.Int32, Menu.iNode);
                dbHelper.AddInParameter(cmd, "@iParent", DbType.Int32, Menu.iParent);
                dbHelper.AddInParameter(cmd, "@vText", DbType.String, Menu.vText);
                dbHelper.AddInParameter(cmd, "@vUrl", DbType.String, Menu.vUrl);
                dbHelper.AddInParameter(cmd, "@vTarget", DbType.String, Menu.vTarget);
                dbHelper.AddInParameter(cmd, "@vImg", DbType.String, Menu.vImg);
                dbHelper.AddInParameter(cmd, "@vMenuNo", DbType.String, Menu.vMenuNo);
                dbHelper.AddInParameter(cmd, "@iOrder", DbType.Int32, Menu.iOrder);
                i = dbHelper.ExecuteNonQuery(cmd);

            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }
            return i;
        }

        ///<summary>
        ///修改菜单列表
        ///</summary>
        ///<param name="sQL">Menu</param>
        ///<returns>返回i</returnss>
        ///Writer：[王成友]
        ///Create Date： [2010-5-21]
        ///</summary>
        public int UpdateMenu(Nova_MOD.Menu Menu)
        {
            try
            {
                strSQL = "update tbMenu set iNode=@iNode,iParent=@iParent,vText=@vText,vUrl=@vUrl,vTarget=@vTarget,vImg=@vImg,vMenuNo=@vMenuNo,iOrder=@iOrder where id=@id";
                cmd = dbHelper.GetSqlStringCommond(strSQL);
                dbHelper.AddInParameter(cmd, "@id", DbType.Int32, Menu.id);
                dbHelper.AddInParameter(cmd, "@iNode", DbType.Int32, Menu.iNode);
                dbHelper.AddInParameter(cmd, "@iParent", DbType.Int32, Menu.iParent);
                dbHelper.AddInParameter(cmd, "@vText", DbType.String, Menu.vText);
                dbHelper.AddInParameter(cmd, "@vUrl", DbType.String, Menu.vUrl);
                dbHelper.AddInParameter(cmd, "@vTarget", DbType.String, Menu.vTarget);
                dbHelper.AddInParameter(cmd, "@vImg", DbType.String, Menu.vImg);
                dbHelper.AddInParameter(cmd, "@vMenuNo", DbType.String, Menu.vMenuNo);
                dbHelper.AddInParameter(cmd, "@iOrder", DbType.Int32, Menu.iOrder);
                i = dbHelper.ExecuteNonQuery(cmd);

            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }
            return i;
        }

        ///<summary>
        ///删除菜单列表
        ///</summary>
        ///<param name="sQL">id</param>
        ///<returns>返回i</returnss>
        ///Writer：[王成友]
        ///Create Date： [2010-5-21]
        ///</summary>
        public int DeleteMenu(string id)
        {
            try
            {
                strSQL = "delete from  tbMenu  where id=@id";
                cmd = dbHelper.GetSqlStringCommond(strSQL);
                dbHelper.AddInParameter(cmd, "@id", DbType.Int32, id);
                i = dbHelper.ExecuteNonQuery(cmd);

            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }
            return i;
        }

        /// <summary>
        /// 删除多个菜单
        /// </summary>
        /// <param name="strID">菜单id</param>
        /// <returns>返回int,0失败,1成功</returns>
        /// Writer:[夏鑫]
        /// Create Date:[2010-5-28]
        public int DeleteAllMenu(string strID)
        {
            try
            {
                strSQL = "delete from tbMenu where id in (" + strID + ") ";
                DbCommand cmd = dbHelper.GetSqlStringCommond(strSQL);
                i = dbHelper.ExecuteNonQuery(cmd);
            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }
            return i;
        }
    }
}

